import datetime
import json
import re

import mysql.connector
import pymssql

from src.core.config import Config
from src.core import str_utils as stru,  global_data as global_varu
from src.core import logger_cust
from src.core import crypt_utils as  cryptU

# import cx_Oracle


class DatabaseOperations:
    def __init__(self):
        if stru.is_not_empty(Config.DB_TYPE):
            # print('重新获取数据库信息')
            self.database = cryptU.aes_decrypt(Config.DB_SID)
            self.password = cryptU.aes_decrypt(Config.DB_PASSWORD)
            self.user = cryptU.aes_decrypt(Config.DB_USERNAME)
            self.database_type = Config.DB_TYPE
            self.server = cryptU.aes_decrypt(Config.DB_HOST)
            # self.json_config_path = 'config.json'
            # self.load_config()
            self.connection = None
            self.cursor = None
            self.login_id = global_varu.login_id


        else:
            logger_cust.error('数据类型有问题')


    def connect(self):
        """
        建立数据库连接
        """
        try:
            if self.database_type == 'sql_server':
                self.connection = pymssql.connect(server=self.server, user=self.user, password=self.password,
                                                  database=self.database)
            elif self.database_type == 'mysql':
                self.connection = mysql.connector.connect(user=self.user, password=self.password, host=self.server,
                                                          database=self.database)
            elif self.database_type == 'oracle':
                logger_cust.error('暂时不支持')
                # cx_Oracle:只支持python 3.10版本，否则安装失败
                # 'cx_Oracle 需要 oracle instant client，请自行下载安装'
                # import cx_Oracle
                # cx_Oracle.init_oracle_client(lib_dir=r"../instantclient_11_2") # instantclient_11_2 需要去官方下载非安装程序
                # self.connection = cx_Oracle.connect(f'{self.user}/{self.password}@{self.server}:{self.database}')
                # oracledb：支持python 3.8以上版本。oracle 11g以上版本
                #  oracledb.init_oracle_client(lib_dir=r"..\instantclient_11_2")
                # conn = oracledb.connect(user="aaa", password="aa", dsn="ip:端口/数据库名")
                # ,Thin=False 默认为True，表示使用Oracle Thin模式，False表示使用Oracle OCI模式。这里不需要
                # oracle 配置完整代码
                """
                project_root = os.getcwd()  # os.path.dirname(os.path.abspath(__file__))
                oracle_dir = os.path.join(project_root, 'instantclient_11_2')
                # print(oracle_dir)
                # oracledb.init_oracle_client(lib_dir=r"../../instantclient_11_2")
                oracledb.init_oracle_client(lib_dir=oracle_dir)
                self.connection = oracledb.connect(user=self.user, password=self.password,
                                                   dsn=self.host + ":" + self.port + "/" + self.database)
                self.cursor = self.connection.cursor()
                """
            else:
                raise ValueError("Invalid database type. Choose from'sql_server','mysql', or 'oracle'")
            self.cursor = self.connection.cursor()
        except Exception as e:
            logger_cust.error(f"连接数据库时出错: {e}")

    def convert_statement(self, statement):
        """
                此方法用于根据数据库的类型将输入的查询语句中的特定占位符进行转换。

                参数：
                - statement (str)：原始的查询语句，可能包含特定的占位符。
                - params (dict)：可能用于替换占位符的值。

                工作流程：
                1. 首先，根据当前数据库的类型（self.database_type）确定转换规则。
                2. 对于'sql_server'类型，将占位符'#{...}'转换为'@{...}'格式。
                3. 对于'mysql'类型，将占位符'#{...}'转换为'%({...})s'格式。
                4. 对于'oracle'类型，将占位符'#{...}'转换为':{...}'格式。
                5. 如果数据库类型不在支持的范围内（'sql_server'、'mysql'、'oracle'），则抛出一个值错误，提示不支持的数据库类型。

                返回：
                - str：转换后的查询语句。
                """

        if self.database_type == 'sql_server':
            pattern = r'#\{(\w+)}'

            def replace(match):
                param_name = match.group(1)
                return f"@{param_name}"

            return re.sub(pattern, replace, statement)
        elif self.database_type == 'mysql':
            pattern = r'#\{(\w+)}'

            def replace(match):
                param_name = match.group(1)
                return f"%({param_name})s"

            return re.sub(pattern, replace, statement)
        elif self.database_type == 'oracle':
            pattern = r'#\{(\w+)}'

            def replace(match):
                param_name = match.group(1)
                return f":{param_name}"

            # print('statement')
            # print(statement)
            return re.sub(pattern, replace, statement)
        else:
            raise ValueError(f"不支持的数据库类型: {self.database_type}")

    def get_column_names(self, table_name):
        """
        根据给定的表名获取表的栏位名（列名）

        参数:
        table_name (str): 要获取列名的表名

        返回:
        list: 包含列名的列表，如果获取失败则返回空列表
        """
        try:

            if self.database_type == 'sql_server':
                statement = f"SELECT COLUMN_NAME Field FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{table_name}'"
            elif self.database_type == 'mysql':
                statement = f"SHOW COLUMNS FROM {table_name}"
            elif self.database_type == 'oracle':
                statement = f"SELECT COLUMN_NAME Field FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = '{table_name.upper()}'"
            else:
                raise ValueError("不支持的数据库类型")

            return query(statement)
        except Exception as e:
            logger_cust.error(f"获取表 {table_name} 的列名时出错: {e}")
            return []

    def is_exists(seft, table_name, where=None, params=None):
        """
               此方法用于检查指定表中满足给定条件的数据是否存在。

               参数:
               - self: 类的实例自身。
               - table_name (str): 要检查的表名。
               - where (str): 筛选条件。
               - params (dict or tuple, 可选): 用于条件中的参数值。

               返回:
               - bool: 如果存在满足条件的数据则返回 True，否则返回 False。
               """
        statement = None
        if where:
            statement = f"SELECT 1 FROM {table_name} {where}"
        else:
            statement = f"SELECT 1 FROM {table_name} "

        retvar = query(statement, params)
        if retvar:
            return True
        else:
            return False

    def query(self, statement, params=None):
        """
        执行查询操作

        参数:
        statement (str): 要执行的查询语句
        params (tuple or dict, 可选): 查询参数

        返回:
        list: 查询结果
        """


        try:
            if not self.connection:
                self.connect()
            converted_statement = self.convert_statement(statement)
            # print('query.converted_statement')
            # print(converted_statement)
            # print(params)
            if not isinstance(converted_statement, str):
                raise TypeError("查询语句必须是字符串")
            if params and not isinstance(params, (tuple, dict)):
                raise TypeError("参数必须是元组或字典")
            if params:
                self.cursor.execute(converted_statement, params)
            else:
                self.cursor.execute(converted_statement)
                # 获取字段名
            columns = [col[0] for col in self.cursor.description]
            results = self.cursor.fetchall()
            """
            retVarDict=None
            retVar=[dict(zip(columns, row)) for row in results]
            if retVar:
                if isinstance(retVar, dict):
                    retVarDict=retVar
                else:
                    retVarDict=[retVar]
            else:
                retVarDict = retVar
            """

            return [dict(zip(columns, row)) for row in results]
            # return [dict(zip(columns, row)) for row in results]
            # return self.cursor.fetchall()
        except Exception as e:
            logger_cust.error(f"执行查询时出错: {e}")
            return []  # 返回一个空列表表示查询失败

    def insert(self, table_name, columns, params=None):
        """
        执行插入操作

        参数:
        table_name (str): 要插入数据的表名
        columns (list): 要插入数据的列名列表，如果为空则从 params 中获取列名
        params (dict, 可选): 包含列名和对应值的字典

        功能:
        1. 如果 columns 为空，从 params 的键中获取要插入的列名。
        2. 如果 columns 不为空而 params 存在，只保留 params 中与 columns 一致的键值对。
        3. 无论 columns 是否存在，只要 `create_time` 和 `creator` 不在其中，就增加创建时间（当前时间）和创建人（固定值或从 params 中获取）的处理。
        4. 构建插入语句并执行插入操作，返回插入的行数。

        返回:
        int: 插入的行数，如果插入操作出错则返回 0
        """
        if not columns:
            columns = list(params.keys()) if params else []
        elif params:
            # 只保留 params 中与 columns 一致的键值对
            params = {key: value for key, value in params.items() if key in columns}

        # 增加创建时间和创建人（如果 columns 中不存在）
        now = datetime.datetime.now()
        create_time = now.strftime('%Y-%m-%d %H:%M:%S')
        creator_by = self.login_id  # 或者从 params 中获取，例如：creator = params.get('creator', 'default_creator')
        print(columns)
        if 'create_date' not in columns:
            columns.append('create_date')
            params['create_date'] = create_time
        if 'creator_by' not in columns:
            columns.append('creator_by')
            params['creator_by'] = creator_by
        if 'last_updated_date' not in columns:
            columns.append('last_updated_date')
            params['last_updated_date'] = create_time
        if 'last_updated_by' not in columns:
            columns.append('last_updated_by')
            params['last_updated_by'] = creator_by
        """
         if 'id' not in columns:
            columns.append('id')
            params['id'] = stru.ulid()
        """

        values_placeholders = ', '.join(['#' + f'{{{col}}}' for col in columns])
        statement = f"INSERT INTO {table_name} ({', '.join(columns)}) VALUES ({values_placeholders})"
        converted_statement = self.convert_statement(statement)
        try:
            if not self.connection:
                self.connect()
            if params:
                self.cursor.execute(converted_statement, params)
            else:
                self.cursor.execute(converted_statement)
            rowcount = self.cursor.rowcount
            self.connection.commit()
            return {'rowcount': rowcount, 'success': True, 'remark': None}
        except Exception as e:
            logger_cust.error(f"执行插入时出错: {e}")
            self.connection.rollback()  # 出现错误时回滚事务
            return {'rowcount': -1, 'success': False, 'remark': f"执行插入时出错: {e}"}

    def modify(self, statement, params=None):
        """
        执行修改操作（如更新、插入）

        参数:
        statement (str): 要执行的修改语句
        params (tuple or dict, 可选): 修改参数

        返回:
        int: 修改的行数
        """
        try:
            if not self.connection:
                self.connect()
            converted_statement = self.convert_statement(statement)
            # print('modify.converted_statement')
            # print(converted_statement)
            # print(params)
            if not isinstance(converted_statement, str):
                raise TypeError("修改语句必须是字符串")
            if params and not isinstance(params, (tuple, dict)):
                raise TypeError("参数必须是元组或字典")
            if params:
                self.cursor.execute(converted_statement, params)
            else:
                self.cursor.execute(converted_statement)
            rowcount = self.cursor.rowcount
            self.connection.commit()
            return {'rowcount': rowcount, 'success': True, 'remark': None}
        except Exception as e:
            logger_cust.error(f"执行修改时出错: {e}")
            self.connection.rollback()  # 出现错误时回滚事务
            return {'rowcount': -1, 'success': False, 'remark': f"执行修改时出错: {e}"}

    def delete(self, statement, params=None):
        """
        执行删除操作

        参数:
        statement (str): 要执行的删除语句
        params (tuple or dict, 可选): 删除参数

        返回:
        int: 删除的行数
        """
        try:
            if not self.connection:
                self.connect()
            converted_statement = self.convert_statement(statement)
            if not isinstance(converted_statement, str):
                raise TypeError("删除语句必须是字符串")
            if params and not isinstance(params, (tuple, dict)):
                raise TypeError("参数必须是元组或字典")
            if params:
                self.cursor.execute(converted_statement, params)
            else:
                self.cursor.execute(converted_statement)
            rowcount = self.cursor.rowcount
            self.connection.commit()
            return {'rowcount': rowcount, 'success': True, 'remark': None}
            # return rowcount
        except Exception as e:
            logger_cust.error(f"执行删除时出错: {e}")
            self.connection.rollback()  # 出现错误时回滚事务
            return {'rowcount': -1, 'success': False, 'remark': None}

    def stored_procedure(self, procedure_name, params=None):
        """
        运行存储过程

        参数:
        procedure_name (str): 存储过程名称
        params (tuple or dict, 可选): 存储过程参数
        """
        result = {'rowcount': 1, 'success': True, 'remark': None}
        try:
            if not self.connection:
                self.connect()
            if not isinstance(procedure_name, str):
                raise TypeError("存储过程名称必须是字符串")
            if self.database_type == 'sql_server':
                if params:
                    self.cursor.execute(
                        f"EXEC {procedure_name} {', '.join([f'@{param} = {value}' for param, value in params.items()])}")
                else:
                    self.cursor.execute(f"EXEC {procedure_name}")
            elif self.database_type == 'oracle':
                if params:
                    self.cursor.callproc(procedure_name, params)
                else:
                    self.cursor.callproc(procedure_name)
            elif self.database_type == 'mysql':
                if params:
                    sql_parts = []
                    query_statement = None

                    for param, value in params.items():

                        if param.startswith('ret_'):
                            sql_parts.append(f"@{param}")
                            if query_statement:
                                query_statement = query_statement + f",@{param} as {param}"
                            else:
                                query_statement = f"select @{param} as {param}"
                        else:
                            sql_parts.append(f"'{value}'")
                            # sql_parts.append(f"@{param} ='{value}'")
                            # sql_parts.append(f"@{param}")

                    statement = f"CALL {procedure_name} ({', '.join(sql_parts)})"

                    self.cursor.execute(statement)

                    result['retDoc'] = self.query(query_statement)
                else:
                    self.cursor.execute(f"CALL {procedure_name}")
            self.connection.commit()
            # return {'rowcount': 1, 'success': True, 'remark': None}
        except Exception as e:
            logger_cust.error(f"运行存储过程时出错: {e}")
            self.connection.rollback()  # 出现错误时回滚事务
            return {'rowcount': -1, 'success': False, 'remark': f"运行存储过程时出错: {e}"}
        return result

    def close(self):
        """
        关闭数据库连接和游标
        """
        try:
            if self.cursor:
                self.cursor.close()
            if self.connection:
                self.connection.close()
        except Exception as e:
            logger_cust.error(f"关闭连接时出错: {e}")


# 提取重复代码为辅助函数
def perform_database_operation(func, statement, params=None):
    db = DatabaseOperations()
    try:
        retvar = func(statement, params)
        db.close()
        return retvar
    except Exception as e:
        logger_cust.error(f"操作时出错: {e}")
    finally:
        db.close()


def convert_params(params):
    try:
        if isinstance(params, list) and len(params) == 1:
            return params[0]
        else:
            return params
    except json.JSONDecodeError as e:
        logger_cust.error(f"解析参数时出错: {e}")
        return {}


def query(statement, params=None):
    """
    执行查询操作

    参数:
    statement (str): 要执行的查询语句
    params (tuple or dict, 可选): 查询参数

    返回:
    list: 查询结果
    """
    if params:
        params = convert_params(params)
    return convert_params(perform_database_operation(DatabaseOperations().query, statement, params))


def modify(statement, params=None):
    """
    执行修改操作

    参数:
    statement (str): 要执行的修改语句
    params (tuple or dict, 可选): 修改参数
    """
    if params:
        params = convert_params(params)
    return perform_database_operation(DatabaseOperations().modify, statement, params)


def delete(statement, params=None):
    """
    执行删除操作

    参数:
    statement (str): 要执行的删除语句
    params (tuple or dict, 可选): 删除参数
    """
    if params:
        params = convert_params(params)

    perform_database_operation(DatabaseOperations().delete, statement, params)


def stored_procedure(procedure_name, params=None):
    """
    执行存储过程

    参数:
    procedure_name (str): 存储过程名称
    params (tuple or dict, 可选): 存储过程参数
    """
    if params:
        params = convert_params(params)

    return perform_database_operation(DatabaseOperations().stored_procedure, procedure_name, params)


def is_exists(table_name, params=None, where=None):
    statement = None
    if where:
        statement = f"SELECT 1 FROM {table_name} {where}"
    else:
        statement = f"SELECT 1 FROM {table_name} "

    # print(statement)

    retvar = query(statement, params)
    if retvar:
        return True
    else:
        return False


def get_column_names(table_name):
    db = DatabaseOperations()
    retvar = db.get_column_names(table_name)
    db.close()
    return retvar


def insert(table_name, params=None, where=None, columns=None):
    it_exists = False
    if where:
        it_exists = is_exists(table_name, params, where, )
    if it_exists:
        return {'rowcount': 0, 'success': False, 'remark': "保存失败，资料已存在！"}
    if params:
        params = convert_params(params)
        ret_column_names = get_column_names(table_name)
        # 插入params 必须在 get_column_names 中
        # params = {key: value for key, value in params.items() if key in ret_column_names['Field']}
        params = {key: value for key, value in params.items() if
                  any(key.lower() in item['Field'].lower() for item in ret_column_names)}
    db = DatabaseOperations()
    try:
        retvar = db.insert(table_name, columns, params)
        db.close()
        return retvar
    except Exception as e:
        logger_cust.error(f"操作时出错: {e}")
    finally:
        db.close()


def modify_table_columns(table_name, params, where):
    """
    此函数用于构建针对指定表的更新语句。

    参数：
    - table_name (str)：要更新的表的名称。
    - params (dict)：包含要更新的列名和对应值的字典。
    - where (str)：更新的条件语句。

    函数执行过程：
    1. 首先尝试将输入的参数 `params` 进行转换和筛选。只保留那些列名在通过 `get_column_names` 函数获取的表列名中，并且不在 `where` 条件语句中的列。
    2. 对于筛选后的列，构建更新语句的列值部分，格式为 `{列名}=#{列名}` 。
    3. 最终构建完整的更新语句，格式为 `UPDATE {表名} SET {列值部分} {条件语句}` ，并打印该语句。

    可能抛出的异常：
    - 如果在参数处理或语句构建过程中发生任何异常，将记录错误信息。
    """
    try:
        update_statement = None
        if params:
            params = convert_params(params)
            ret_column_names = get_column_names(table_name)
            # 插入params 必须在 get_column_names 中
            # params = {key: value for key, value in params.items() if key in ret_column_names['Field']}
            params = {key: value for key, value in params.items() if
                      any(key.lower() in item['Field'].lower() for item in ret_column_names)}

        if params:
            columns = []
            for key, value in params.items():
                wherekey = f"#{{{key}}}"
                if not re.search(wherekey, where, re.IGNORECASE):
                    columns.append(f"{key}=#{{{key}}}")
            if columns:
                # 增加创建时间和创建人（如果 columns 中不存在）
                now = datetime.datetime.now()
                create_time = now.strftime('%Y-%m-%d %H:%M:%S')
                creator_by = global_varu.login_id  # 或者从 params 中获取，例如：creator = params.get('creator', 'default_creator')

                if 'last_updated_date' not in columns:
                    columns.append('last_updated_date=#{last_updated_date}')
                    params['last_updated_date'] = create_time
                if 'last_updated_by' not in columns:
                    columns.append('last_updated_by=#{last_updated_by}')
                    params['last_updated_by'] = creator_by

                update_statement = f"UPDATE {table_name} SET {', '.join(columns)} {where}"

        if update_statement:
            retvar = modify(update_statement, params)
        else:
            retvar = {'rowcount': 0, 'success': False, 'remark': "修改失败，无修改栏位名！"}
        return retvar

    except Exception as e:
        logger_cust.error(f"更新表栏位时出错: {e}")
        return {'rowcount': 0, 'success': False, 'remark': f"更新表栏位时出错: {e}"}


def deletion_mark(table_name, params=None, where=None):
    """
       此函数用于处理表中数据的删除标记更新操作。

       参数:
       - table_name (str): 要操作的表名。
       - params (dict): 包含要更新的列名和对应值的字典。
       - where (str): 筛选条件。

       工作流程:
       1. 首先处理 `where` 条件，如果已有条件则添加 `and deletion_mark=0`，否则设置默认条件为 `where 1=1 and deletion_mark=0`。
       2. 对 `params` 进行处理和筛选，只保留与通过 `get_column_names` 获取的表列名匹配的键值对。
       3. 构建更新语句的列值部分，对于一些特定列（如 `last_updated_date`、`last_updated_by`、`deletion_date`、`deletion_by`、`deletion_reason`、`deletion_mark`），如果它们不在列值中，则添加并设置默认值。
       4. 根据是否成功构建更新语句执行 `modify` 操作，并返回相应的结果。
       5. 如果在任何步骤中出现异常，记录错误并返回包含错误信息的字典。

       返回:
       - dict: 包含更新操作的结果信息，如 `rowcount`（更新的行数）、`success`（是否成功）和 `remark`（备注信息）。
       """
    try:
        if where:
            where += ' and deletion_mark=0'
        else:
            where = 'where 1=1  and deletion_mark=0'
        update_statement = None
        if params:
            params = convert_params(params)
            ret_column_names = get_column_names(table_name)
            # 插入params 必须在 get_column_names 中
            # params = {key: value for key, value in params.items() if key in ret_column_names['Field']}
            params = {key: value for key, value in params.items() if
                      any(key.lower() in item['Field'].lower() for item in ret_column_names)}

        if params:
            columns = []
            for key, value in params.items():
                wherekey = f"#{{{key}}}"
                if not re.search(wherekey, where, re.IGNORECASE):
                    columns.append(f"{key}=#{{{key}}}")
            if columns:
                # 增加创建时间和创建人（如果 columns 中不存在）
                now = datetime.datetime.now()
                create_time = now.strftime('%Y-%m-%d %H:%M:%S')
                creator_by = global_varu.login_id  # 或者从 params 中获取，例如：creator = params.get('creator', 'default_creator')
                # print('删除栏位')
                # print(columns)

                if 'last_updated_date=#{last_updated_date}' not in columns:
                    columns.append('last_updated_date=#{last_updated_date}')
                    params['last_updated_date'] = create_time
                if 'last_updated_by=#{last_updated_by}' not in columns:
                    columns.append('last_updated_by=#{last_updated_by}')
                    params['last_updated_by'] = creator_by

                if 'deletion_date=#{deletion_date}' not in columns:
                    columns.append('deletion_date=#{deletion_date}')
                    params['deletion_date'] = create_time
                if 'deletion_by=#{deletion_by}' not in columns:
                    columns.append('deletion_by=#{deletion_by}')
                    params['deletion_by'] = creator_by
                # deletion_reason
                if 'deletion_reason=#{deletion_reason}' not in columns:
                    columns.append('deletion_reason=#{deletion_reason}')
                    params['deletion_reason'] = "无删除原因"
                if 'deletion_mark=#{deletion_mark}' not in columns:
                    columns.append('deletion_mark=#{deletion_mark}')
                    params['deletion_mark'] = 1

                update_statement = f"UPDATE {table_name} SET {', '.join(columns)} {where}"

        if update_statement:
            retvar = modify(update_statement, params)
        else:
            retvar = {'rowcount': 0, 'success': False, 'remark': "修改失败，无修改栏位名！"}
        return retvar

    except Exception as e:
        logger_cust.error(f"更新表栏位时出错: {e}")
        return {'rowcount': 0, 'success': False, 'remark': f"更新表栏位时出错: {e}"}


if __name__ == '__main__':
    logger_cust.error("Handling root debug")

    """
    statement = 'SELECT * FROM sys_user WHERE login_id = #{login_id}'
    params = {'login_id': '10203'}
    retvar = query(statement, params)
    if retvar:
        print(retvar)

    
   

    statement = "sys_user"
    params = [{'login_id': '10203', 'USER_name': 'sen shan', "xxx": None}]
    where = 'where 1=1 and deletion_mark=0 and login_id= #{login_id}'
    retvar = deletion_mark(statement, params, where)
    retvar = modify_table_columns(statement, params, where)
    where = 'where 1=1  and login_id= #{login_id}'
    retvar = insert(statement, params, where)
    # print(retvar)
    # retvar = insert(statement, params)
    # print(retvar)

    # statement = 'SHOW COLUMNS FROM sys_user'
    # retvar = query(statement)
    # retvar = get_column_names('sys_user')
    # if retvar:
    #     print(retvar)

    statement = 'SELECT id,login_id,USER_name FROM sys_user'
    params = [{'login_id': '10203', 'USER_name': 'sen shan'}]
    where = 'where 1=1 and login_id= %(login_id)s'
    statement = 'SELECT * FROM sys_user WHERE login_id = #{login_id}'
    # params = {'login_id': '10203'}
    retvar = query(statement, params)
    if retvar:
        print(retvar)
         """

    """
    retvar = query(statement, params)
    print('-------1.0-----')
    print(retvar)
    print('-------2.0-----')
    statement = 'SELECT * FROM sys_user'
    retvar = query(statement)
    print(retvar)
    statement = "sys_user"
    params = [{'login_id': '10203', 'USER_name': 'sen shan'}]

    print(params)
    # retvar = insert(statement, params)
    print(retvar)
    """
